package com.ly.demo.taach.jdbc.impl;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.ly.demo.taach.jdbc.IDBHelper;
import com.ly.demo.taach.jdbc.bean.Page;

public class DBHelper extends IDBHelper {

	/**
	 * 1. 加载驱动
	 * 		驱动一次	类设计==> 静态块
	 * 		读取配置文件
	 * 2. 获取连接
	 * 3. 创建语句
	 * 4. 执行语句
	 * 5. 关闭连接
	 */

	static {
		// 读取配置文件
		// Map 集合 特点: 无序不重复, 键值对   Properties 可以文件存储
		Properties prop = new Properties();
		String path = "jdbc.properties";
		try (InputStream inStream = DBHelper.class.getClassLoader().getResourceAsStream(path)) {
			// 假设配置文件名为 jdbc.properties, 路径 在 src目录下
			// 类加载机制, 加载项目的文件(字节码, 配置文件...)
			// getClassLoader() 获取类加载器
			// getResourceAsStream 读取文件返回字节流
			;
			// 加载配置文件
			prop.load(inStream);
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
			String driver = prop.getProperty("driver");
			Class.forName(driver);
		} catch (Exception e) {
			// 异常转型(运行期异常)
			throw new RuntimeException(e);
		}
	}

	@Override
	public Connection getConnection() throws SQLException {
		return conn;
	}

	@Override
	public void close() throws SQLException {
		conn.close();
	}

	@Override
	public void commit() throws SQLException {
		conn.commit();
	}

	@Override
	public void rollback() throws SQLException {
		conn.rollback();
	}

	@Override
	public int update(String sql, Object... params) throws SQLException {
		PreparedStatement ps = preparedStatement(sql, params);
		return ps.executeUpdate();
	}

	@Override
	public Object insertOne(String sql, String primaryKeyName, Object... params) throws SQLException {
		System.out.println("SQL:" + sql);
		System.out.println("参数:" + Arrays.deepToString(params));
		// 创建语句, 同时设置主键字段名
		PreparedStatement ps = conn.prepareStatement(sql, new String[] { primaryKeyName });
		// 执行语句
		for (int i = 0; i < params.length; i++) {
			ps.setObject(i + 1, params[i]);
		}
		ps.executeUpdate();
		ResultSet rs = ps.getGeneratedKeys();
		rs.next();
		return rs.getObject(1);
	}

	@Override
	public List<Map<String, Object>> selectListMap(String sql, Object... params) throws SQLException {
		PreparedStatement ps = preparedStatement(sql, params);

		ResultSet rs = ps.executeQuery();

		// 元(Meta)数据对象 => 描述结果集的对象 
		ResultSetMetaData rsmd = rs.getMetaData();

		List<Map<String, Object>> list = new ArrayList<>();

		while (rs.next()) {
			Map<String, Object> row = new LinkedHashMap<>();
			// 如何知道有几列, 每个列的名字什么 ?
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				String column = rsmd.getColumnName(i + 1);
				Object value = rs.getObject(column);
				row.put(column, value);
			}
			list.add(row);
		}
		return list;
	}

	/**
	 * select * from emp where empno=?;  ==> 0~1行
	 */
	@Override
	public Map<String, Object> selectOneMap(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> list = selectListMap(sql, params);
		return list.isEmpty() ? null : list.get(0);
	}

	/**
	 * select count(*) cnt from emp;  ==> 1行 1列
	 */
	@Override
	public Object selectValue(String sql, Object... params) throws SQLException {
		Map<String, Object> row = selectOneMap(sql, params);
		// 返回第一个字段值
		return row.values().iterator().next();
	}

	/**
	 * 创建语句对象 + 设置参数
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement preparedStatement(String sql, Object... params) throws SQLException {
		System.out.println("SQL:" + sql);
		System.out.println("参数:" + Arrays.deepToString(params));
		// 创建语句
		PreparedStatement ps = conn.prepareStatement(sql);
		// 执行语句
		for (int i = 0; i < params.length; i++) {
			ps.setObject(i + 1, params[i]);
		}
		return ps;
	}

	@Override
	public Page<Map<String, Object>> selectPage(String sql, int page, int size, Object... params) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}
